package com.brother.database;

import java.beans.PropertyVetoException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import org.apache.log4j.Logger;

import com.mchange.v2.c3p0.ComboPooledDataSource;



public class DataBase {

public static Properties props = null;

private static Logger log = Logger.getLogger(DataBase.class);

private static ComboPooledDataSource dataSource = null;
static{
	InputStream in = DataBase.class.getResourceAsStream("/jdbc.properties");
	props = new Properties();
	try {
		props.load(in);
	} catch (IOException e) {
		
	}finally {
		if(in != null)
			try {
				in.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
	}
	
	String url = props.getProperty("jdbc.url");
	String username = props.getProperty("jdbc.username");
	String password = props.getProperty("jdbc.password");
	String driver = props.getProperty("jdbc.driver");
	
	dataSource = new ComboPooledDataSource(true); 
	 try {
		 dataSource.setDriverClass(driver);
	} catch (PropertyVetoException e) {
		e.printStackTrace();
	} 
	 //loads the jdbc driver 
	dataSource.setJdbcUrl(url);
	dataSource.setUser(username); 
	dataSource.setPassword(password); 
	dataSource.setMaxPoolSize(140);
	dataSource.setMinPoolSize(20);
	dataSource.setMaxIdleTime(0);// second
	dataSource.setIdleConnectionTestPeriod(300);// second
	dataSource.setTestConnectionOnCheckin(true);
	dataSource.setAcquireRetryDelay(1000);// millisecond
	dataSource.setMaxStatements(0);
	dataSource.setMaxStatementsPerConnection(0);
//	dataSource.setUnreturnedConnectionTimeout(300);// second
//	dataSource.setDebugUnreturnedConnectionStackTraces(true);
	dataSource.setAutomaticTestTable("testtable");
	dataSource.setBreakAfterAcquireFailure(false);
}

private static final ThreadLocal<Connection> tl = new ThreadLocal<Connection>();

public static Connection getConnection() {
	Connection conn = null;
	
	try {
		
		conn = tl.get();
		if(conn == null || conn.isClosed()){
			if(log.isDebugEnabled()){
				log.debug("Getting the thread connection...");
			}
			for(int i=0; i < 20;i++){
				conn = dataSource.getConnection();
				if(!conn.isClosed()){
					tl.set(conn);
					break;
				}
			}
		}
	} catch (SQLException e) {
		Logger.getLogger(DataBase.class).error("",e);
	}
	
	return conn;
}

public static void closeConnection(Connection conn) {
	try {
		if(conn != null) {
			conn.close();
		}
	} catch (SQLException e) {
		Logger.getLogger(DataBase.class).error(e);
	}
}
	
public static int update(String sql) {
	
		try {
			Connection conn = getConnection();
			Statement stmt = conn.createStatement();
			return stmt.executeUpdate(sql);
		} catch (Exception e) {
			Logger.getLogger(DataBase.class).error(sql,e);
			return -1;
		}
}

public static ResultSet query(String sql) throws SQLException {
	Connection conn = getConnection();
	Statement stmt = conn.createStatement();
	return stmt.executeQuery(sql);
}

public static void closeConnection() {
	//  Now it is replaced with the closeThreadConnection
	closeThreadConnection();
}

public static void closeThreadConnection(){
	try {
		
		
		if(tl.get() != null && !tl.get().isClosed()) {
			if(log.isDebugEnabled()){
				log.debug("Closing the thread connection...");
			}
			tl.get().close();
		}
	} catch (SQLException e) {
		log.error("",e);
	}
}

}
